/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;
import java.sql.*;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.DefaultTableModel;
import POJO.GhePOJO;
import Utils.MySqlDataAccessHelper;
 /**
 * 
 * @author KimLoan
 */
public class GheDAO {
    public boolean themGhe(GhePOJO gh) {
        boolean kq = false;
        String sql = String.format("INSERT INTO ghe(idPhong,LoaiGhe,TinhTrangGhe)"
                + " VALUES(%d,%d,%d,%d)", gh.getMaPhong(), gh.getLoaiGhe(), gh.getTrangThai());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;//Them Ghe thanh cong
        }

        helper.close();
        return kq;
    }

    /**
     * Phuong Thuc Xoa Ghe
     * @return
     */
    public boolean xoaGhe(String maGhe) {

        boolean kq = false;
        try {
            String sql = "delete from ghe where idGhe=" + maGhe;
            // String sql = "DELETE FROM Ghe WHERE maGhe ='"+  maGhe+"'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            int n = helper.executeUpdate(sql);
            if (n >= 1) {
                kq = true;//xoa thanh cong
            }
            helper.close();
            return kq;
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            return kq;
        }
    }

    /**
     * Phuong Thuc Cap Nhat Ghe
     */
    public boolean capNhatGhe(GhePOJO gh) {
        boolean kq = false;

        String sql = String.format(
                "UPDATE ghe SET idPhong=%d,LoaiGhe=%d,TinhTrangGhe=%d"
                + " WHERE idGhe=%d ", gh.getMaPhong(), gh.getLoaiGhe(), gh.getTrangThai(),gh.getMaGhe());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;//Cap Nhat Ghe thanh cong
        }
        helper.close();
        return kq;
    }

    /**
     * Phuong Thuc Xem Danh Ghe
     * @return
     */
    public ArrayList<GhePOJO> layDanhGheCacMaGhe() {
        ArrayList<GhePOJO> ds = new ArrayList<GhePOJO>();
        try {
            String sql = "SELECT * FROM ghe";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                GhePOJO gh = new GhePOJO();
                gh.setMaGhe(rs.getInt("idGhe"));
                gh.setMaPhong(rs.getInt("idPhong"));
                gh.setLoaiGhe(rs.getInt("LoaiGhe"));
                gh.setTrangThai(rs.getInt("TinhTrangGhe"));
                ds.add(gh);
            }
            helper.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    /**
     * Phuong Thuc Lay Thong Tin Ghe
     * @param maGhe
     * @return
     */
    public GhePOJO layThongTinGhe(String maGhe) {
        GhePOJO Ghe = null;
        String sql = String.format("SELECT * FROM ghe WHERE idGhe=%d", maGhe);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                Ghe = new GhePOJO();
                Ghe.setMaGhe(rs.getInt("idGhe"));
                Ghe.setMaPhong(rs.getInt("idPhong"));
                Ghe.setLoaiGhe(rs.getInt("LoaiGhe"));
                Ghe.setTrangThai(rs.getInt("TinhTrangGhe"));

            }
            helper.close();
        } catch (SQLException ex) {

            System.out.println(ex.getMessage());
        }
        return Ghe;
    }
    //Ham Lay danh Ghe theo the loai

    public ArrayList<GhePOJO> layDSGhe_TheoTheLoai(int maTheLoai) {

        ArrayList<GhePOJO> dsGhe = new ArrayList<GhePOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LayDSGhe_TheoTheLoai(?)}");
            statement.setInt(1, maTheLoai);

            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {
                GhePOJO Ghe = new GhePOJO();
                Ghe.setMaGhe(rs.getInt("idGhe"));
                Ghe.setMaPhong(rs.getInt("idPhong"));
                Ghe.setLoaiGhe(rs.getInt("LoaiGhe"));
                Ghe.setTrangThai(rs.getInt("TinhTrangGhe"));
                dsGhe.add(Ghe);

            }
            helper.close();
            return dsGhe;
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
            return dsGhe;
        }
    }

    public ArrayList<GhePOJO> timKiemGhe_TheoTenGhe(String _tenGhe) {

        ArrayList<GhePOJO> ds = new ArrayList<GhePOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LayGheTheoTenGhe(?)}");
            statement.setString(1, _tenGhe);
            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {
                GhePOJO Ghe = new GhePOJO();
                Ghe.setMaGhe(rs.getInt("idGhe"));
                Ghe.setMaPhong(rs.getInt("idPhong"));
                Ghe.setLoaiGhe(rs.getInt("LoaiGhe"));
                Ghe.setTrangThai(rs.getInt("TinhTrangGhe"));
                ds.add(Ghe);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return ds;
    }
    
}
